﻿using Dapper;
using PatientRegisterService.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
namespace PatientRegisterService.DAO
{
    public class PatientRegistDAO
    {
        static string config = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["SqlConnStr"].ConnectionString;
        IDbConnection connection = new SqlConnection(config);

        //号源查询
        public List<NumSourceInfo> GetOrderNumInfoDAO(string beginDate, string endDate, string YSCode, string KSCode, bool isRemain)
        {
            //万达说他们不做号源是否为0的查询参数判断直接给他们不为0的,但怕他们反悔我先留着之前的
            string remainNum;
            //if (isRemain)
            //{
            remainNum = @" AND CASE WHEN ISNULL(B.LiveCount, 0) <= A.totNum - A.totNum2        
                                                   THEN A.totNum2 - ISNULL(B.ReservationCount, 0)  
                                                   ELSE A.totNum - ISNULL(B.LiveCount, 0)          
                                                        - ISNULL(B.ReservationCount, 0)            
                              END > 0 ";
            //}
            //else { remainNum = ""; }
            if (!YSCode.Equals("*"))
            {
                remainNum = remainNum + " AND A.YSCode <> '*' ";
            }
            string sql = @"DECLARE @BegDate DATE;
                           DECLARE @EndDate DATE;
                           DECLARE @YSCode VARCHAR(20);
                           DECLARE @KSCode VARCHAR(20);
                           SET DATEFIRST 1;
                           SET @BegDate = @begin;
                           SET @EndDate = @end;
                           SET @YSCode = @code;
                           SET @KSCode = @codeKS;
                           SELECT  A.KeyNo AS scheduleId,
                                   A.KeyNo AS numSourceId,
                                   CASE WHEN A.YSCode = '*' THEN 2
                                        ELSE 1
                                   END AS orderType ,
                                   RTRIM(A.YSCode) AS resourceCode,
                                   '43120477700' AS hosOrgCode,
                                   '云南省第三人民医院' AS hosName,
                                   RTRIM(C.LBNAME) AS oneDeptName ,
                                   RTRIM(A.KSName) AS deptName ,
                                   CASE WHEN A.YSCode = '*' THEN RTRIM(A.KSName)
                                        ELSE RTRIM(A.YSName)
                                   END AS resourceName ,
                                   A.CheckMoney1 + A.CheckMoney2 AS visitCost ,
                                   CONVERT(varchar(20),CASE WHEN DATEPART(WEEKDAY, GETDATE()) < A.Weekday
                                         THEN DATEADD(DAY, A.Weekday - DATEPART(WEEKDAY, GETDATE()),CAST(GETDATE() AS DATE))
                                         ELSE DATEADD(DAY,7 + A.Weekday - DATEPART(WEEKDAY, GETDATE()),CAST(GETDATE() AS DATE))
                                   END,23) AS scheduleDate ,
                                   A.TimeInterval AS timeRange,
                                   CASE WHEN ISNULL(B.LiveCount, 0) <= A.totNum - A.totNum2
                                        THEN A.totNum2 - ISNULL(B.ReservationCount, 0)
                                        ELSE A.totNum - ISNULL(B.LiveCount, 0)
                                             - ISNULL(B.ReservationCount, 0)
                                   END AS remainNum ,
                                   A.totNum2 AS reserveOrderNum ,
                                   CASE CheckGroupName
                                     WHEN '副主任医师挂号' THEN '102'
                                     WHEN '副主任医师挂号（减免）' THEN '102'
                                     WHEN '急诊挂号' THEN '999'
                                     WHEN '普通挂号' THEN '100'
                                     WHEN '主任医师挂号' THEN '102'
                                     WHEN '主任医师挂号（减免）' THEN '102'
                                   END AS orderNumType ,
                                   NULL AS otherCost ,
                                   NULL AS yl1 ,
                                   NULL AS yl2 ,
                                   NULL AS yl3
                           FROM    MZSchedule A ( NOLOCK )
                                   LEFT JOIN ( SELECT  ISNULL(Y.YSCode, X.DoctCode) AS YSCode ,
                                                       CAST(ISNULL(Y.RegisterDate, X.ReservationDate) AS DATE) AS SourceDate ,
                                                       SUM(CASE WHEN X.Id IS NULL THEN 1
                                                                ELSE 0
                                                           END) AS LiveCount ,
                                                       SUM(CASE WHEN X.Id IS NOT NULL THEN 1
                                                                ELSE 0
                                                           END) AS ReservationCount
                                               FROM    AP_ReservationSheet X ( NOLOCK )
                                                       FULL OUTER JOIN MZRegisterSheet Y ( NOLOCK ) ON X.FPNum = Y.FPNum
                                               WHERE   ISNULL(Y.YSCode, X.DoctCode) LIKE '%' + @YSCode
                                                       AND ISNULL(Y.KSCode,X.DeptCode) LIKE '%' + @KSCode
                                                       AND X.DelDate IS NULL
                                                       AND Y.DelDate IS NULL
                                                       AND CAST(ISNULL(Y.RegisterDate, X.ReservationDate) AS DATE) > @BegDate
                                                       AND CAST(ISNULL(Y.RegisterDate, X.ReservationDate) AS DATE) <= @EndDate
                                               GROUP BY ISNULL(Y.YSCode, X.DoctCode) ,
                                                       CAST(ISNULL(Y.RegisterDate, X.ReservationDate) AS DATE)
                                             ) B ON A.YSCode = B.YSCode
                                                    AND CASE WHEN DATEPART(WEEKDAY, GETDATE()) <= A.Weekday
                                                             THEN DATEADD(DAY,
                                                                          A.Weekday - DATEPART(WEEKDAY,
                                                                                         GETDATE()),
                                                                          CAST(GETDATE() AS DATE))
                                                             ELSE DATEADD(DAY,
                                                                          7 + A.Weekday
                                                                          - DATEPART(WEEKDAY, GETDATE()),
                                                                          CAST(GETDATE() AS DATE))
                                                        END = B.SourceDate
                                   JOIN KSCODE C ( NOLOCK ) ON A.KSCode = C.CODE
                           WHERE   ISNULL(A.Status, 0) <> 1
                                   AND A.KSCode LIKE @KSCode
                                   AND A.YSCode LIKE
                          @YSCode " +
                         remainNum +
                         @"        AND CASE WHEN DATEPART(WEEKDAY, GETDATE()) < A.Weekday
                                            THEN DATEADD(DAY, A.Weekday - DATEPART(WEEKDAY, GETDATE()),CAST(GETDATE() AS DATE))
                                            ELSE DATEADD(DAY,7 + A.Weekday - DATEPART(WEEKDAY, GETDATE()),CAST(GETDATE() AS DATE))
                                      END >= @BegDate
                                  AND CASE WHEN DATEPART(WEEKDAY, GETDATE()) < A.Weekday
                                           THEN DATEADD(DAY, A.Weekday - DATEPART(WEEKDAY, GETDATE()),CAST(GETDATE() AS DATE))
                                           ELSE DATEADD(DAY,7 + A.Weekday - DATEPART(WEEKDAY, GETDATE()),CAST(GETDATE() AS DATE))
                                      END <= @EndDate
                          ORDER BY A.KSCode ,
                                  A.YSCode ,
                                  CASE WHEN DATEPART(WEEKDAY, GETDATE()) < A.Weekday
                                       THEN DATEADD(DAY, A.Weekday - DATEPART(WEEKDAY, GETDATE()),CAST(GETDATE() AS DATE))
                                       ELSE DATEADD(DAY,7 + A.Weekday - DATEPART(WEEKDAY, GETDATE()),CAST(GETDATE() AS DATE))
                                  END";
            return connection.Query<NumSourceInfo>(sql, new { begin = beginDate, end = endDate, code = YSCode, codeKS = KSCode }).ToList();
        }
        //号源预约
        public bool SubmitOrderByUserInfoADO(string CallNo, string CallName, DateTime ReservationDate, int ScheduleId, string ScheduleIdStr, string CardNo, int CardType, string IdCardNo, string Name, int Sex, DateTime BirthDay, string Tel, string Adress, string TransNo, out string errorMessage, out int KeyNo,
            string payMode, string patient_type, string patient_id, string userCardType, string replaceUserCardType, string replaceUserCardId, string replaceUserName, string ipAddr, string macAddr, string startTime, string endTime, string userCardId)
        {
            string sql = @"DECLARE @PatientId INT
                           DECLARE @CardNo VARCHAR(20)
                           DECLARE @CardType INT
                           DECLARE @CALLNO INT
                           DECLARE @PatientName VARCHAR(20)
                           DECLARE @SEX INT
                           DECLARE @BIRTHDAY DATE
                           DECLARE @ADDRESS VARCHAR(50)
                           DECLARE @TEL VARCHAR(20)
                           DECLARE @IDCARDNO VARCHAR(18)
                           DECLARE @CALLNAME VARCHAR(20)
                           DECLARE @CurrentDate DATETIME
                           DECLARE @TRANSNO_IN VARCHAR(50)
                           DECLARE @RESERVATIONDATE_IN DATE
                           DECLARE @ScheduleId INT
                           DECLARE @DEPTCODE_IN VARCHAR(20)
                           DECLARE @DOCTCODE_IN VARCHAR(20)
                           DECLARE @TID_IN VARCHAR(20)
                           DECLARE @DOCTNAME_HIS VARCHAR(20)
                           DECLARE @TITLECODE_HIS VARCHAR(20)
                           DECLARE @TITLENAME_HIS VARCHAR(20)
                           DECLARE @GROUPPRICE_CALC MONEY
                           DECLARE @DEPTNAME_HIS VARCHAR(20)
                           DECLARE @SPEC_HIS VARCHAR(256)
                           DECLARE    @CLINICCODE_IN VARCHAR(20)
                           DECLARE @GROUPNAME_HIS VARCHAR(20)
                           DECLARE @CheckMoney1 MONEY
                           DECLARE @CheckMoney2 MONEY
                           DECLARE @payMode             varchar(50)       
                           DECLARE @patient_type        varchar(50)
                           DECLARE @patient_id          varchar(50)
                           DECLARE @userCardType        varchar(50)
                           DECLARE @replaceUserCardType varchar(50)
                           DECLARE @replaceUserCardId   varchar(50)
                           DECLARE @replaceUserName     varchar(50)
                           DECLARE @ipAddr              varchar(50)
                           DECLARE @macAddr             varchar(50)
                           DECLARE @startTime           varchar(50)
                           DECLARE @endTime             varchar(50)
                           DECLARE @userCardId          varchar(50)
                           DECLARE @scheduleIdStr       varchar(50)

                           
                           SET @CardNo = @CardNo_P
                           SET @CardType = @CardType_P
                           SET @CALLNO = @CALLNO_P
                           SET @PatientName = @PatientName_P
                           SET @SEX = @SEX_P
                           SET @BIRTHDAY = @BIRTHDAY_P
                           SET @ADDRESS = @ADDRESS_P
                           SET @TEL = @TEL_P
                           SET @IDCARDNO = @IDCardNo_P
                           SET @CALLNAME = @CallName_P
                           SET @CurrentDate = GETDATE()
                           SET @RESERVATIONDATE_IN= @RESERVATIONDATE_IN_P
                           SET @ScheduleId = @ScheduleId_P
                           SET @TRANSNO_IN = @TRANSNO_IN_P
                           SET @payMode             = @payMode_P
                           SET @patient_type        = @patient_type_P
                           SET @patient_id          = @patient_id_P
                           SET @userCardType        = @userCardType_P
                           SET @replaceUserCardType = @replaceUserCardType_P
                           SET @replaceUserCardId   = @replaceUserCardId_P
                           SET @replaceUserName     = @replaceUserName_P
                           SET @ipAddr              = @ipAddr_P
                           SET @macAddr             = @macAddr_P
                           SET @startTime           = @startTime_P
                           SET @endTime             = @endTime_P
                           SET @userCardId          = @userCardId_P
                           SET @scheduleIdStr       = @scheduleIdStr_P

                           SELECT @PatientId=PatientId FROM AP_BINDINGCARD (NOLOCK) WHERE CardNo=@CardNo AND CardType=@CardType AND BINDINGFROMTYPE=@CALLNO AND UNBINDINGDATE IS NULL
                           
                           IF @PatientId IS NULL
                           BEGIN
                               IF EXISTS(SELECT PATIENTID FROM PATIENTBASE (NOLOCK) WHERE PERSONID=@IDCARDNO AND PATIENTNAME=@PATIENTNAME AND SEX=CASE WHEN (@SEX)=1 THEN  '男'   WHEN (@SEX)=2 THEN '女' END)
                               BEGIN
                                   SELECT top 1 @PATIENTID=PATIENTID FROM PATIENTBASE (NOLOCK) WHERE PERSONID=@IDCARDNO AND PATIENTNAME=@PATIENTNAME AND SEX=CASE WHEN  (@SEX)  =1 THEN '男' WHEN (@SEX)=2 THEN '女' END
                                 order by BINDINGDATE desc,operdate desc
                                   INSERT INTO AP_BINDINGCARD
                                          (CARDNO
                                          ,CARDTYPE
                                          ,PATIENTID
                                          ,IDCARDNO
                                          ,NAME
                                          ,SEX
                                          ,BIRTHDAY
                                          ,TEL
                                          ,ADDRESS
                                          ,BINDINGFROMTYPE
                                          ,BINDINGFROMNAME
                                          ,BINDINGDATE
                                          ,BINDINGTRANSNO
                                          )
                                    VALUES
                                          (@CardNo
                                          ,@CardType
                                          ,@PATIENTID
                                          ,@IDCARDNO
                                          ,@PATIENTNAME
                                          ,@SEX
                                          ,@BIRTHDAY
                                          ,@TEL
                                          ,@ADDRESS
                                          ,@CALLNO
                                          ,@CALLNAME
                                          ,@CurrentDate
                                          ,@TRANSNO_IN
                                          )
                               END ELSE
                               BEGIN                           
                                   EXEC GETUNIQUENO 26,@NEWUNIQUENO=@PATIENTID OUTPUT
                                   INSERT INTO PATIENTBASE
                                               (PATIENTID
                                               ,PATIENTNAME
                                               ,PYM
                                               ,SEX
                                               ,BIRTHDAY
                                               ,ADDRESS
                                               ,TEL
                                               ,PERSONID
                                               ,OPERNAME
                                               ,OPERDATE
                                               ,CREATEDATE
                                               ,CREATEOPER
                                               ,BINDINGDATE
                                               ,BINDINGOPERNAME)
                                           VALUES
                                               (@PATIENTID
                                               ,@PATIENTNAME
                                               ,'WD'
                                               ,CASE WHEN (@SEX)=1 THEN '男' WHEN (@SEX)=2 THEN '女' END
                                               ,@BIRTHDAY
                                               ,@ADDRESS
                                               ,@TEL
                                               ,@IDCARDNO
                                               ,@CALLNAME
                                               ,@CurrentDate
                                               ,@CurrentDate
                                               ,@CALLNAME
                                               ,null
                                               ,@CALLNAME
                                               )
                                   INSERT INTO AP_BINDINGCARD
                                          (CARDNO
                                          ,CARDTYPE
                                          ,PATIENTID
                                          ,IDCARDNO
                                          ,NAME
                                          ,SEX
                                          ,BIRTHDAY
                                          ,TEL
                                          ,ADDRESS
                                          ,BINDINGFROMTYPE
                                          ,BINDINGFROMNAME
                                          ,BINDINGDATE
                                          ,BINDINGTRANSNO
                                          )
                                    VALUES
                                          (@CardNo
                                          ,@CardType
                                          ,@PATIENTID
                                          ,@IDCARDNO
                                          ,@PATIENTNAME
                                          ,@SEX
                                          ,@BIRTHDAY
                                          ,@TEL
                                          ,@ADDRESS
                                          ,@CALLNO
                                          ,@CALLNAME
                                          ,@CurrentDate
                                          ,@TRANSNO_IN
                                          )
                               END 
                           END
                           IF @PatientId IS NULL
                           BEGIN
                               SELECT -1 AS Result,'获取病人Id失败' AS ResultText
                               RETURN;
                           END
                           IF (@RESERVATIONDATE_IN)<=GETDATE() OR @RESERVATIONDATE_IN>GETDATE()+8
                           BEGIN
                               SELECT -1 AS RESULT,'预约日期超出范围！' AS RESULTTEXT
                               RETURN
                           END
                           IF EXISTS(SELECT Id FROM AP_ReservationSheet(NOLOCK) WHERE ReservationFromType=@CALLNO AND TRANSNO=@TRANSNO_IN)
                           BEGIN
                               SELECT -1 AS RESULT,'TransNo重复！' AS RESULTTEXT
                               RETURN
                           END
                           IF EXISTS(SELECT Id FROM AP_OrderSheet(NOLOCK) WHERE OrderFromType=@CALLNO AND TRANSNO=@TRANSNO_IN)
                           BEGIN
                               SELECT -1 AS RESULT,'TransNo重复！' AS RESULTTEXT
                               RETURN
                           END
                           IF EXISTS(SELECT Id FROM AP_RESERVATIONSHEET(NOLOCK) WHERE PATIENTID=@PATIENTID AND RESERVATIONDATE=@RESERVATIONDATE_IN AND DELDATE IS NULL)
                           BEGIN
                               SELECT -1 AS RESULT,'同一天内只能预约一次！' AS RESULTTEXT
                               RETURN
                           END
                           DECLARE @TotNum INT
                           DECLARE @TotNum2 INT
                           DECLARE @OtherAlreadyCount INT
                           SET DATEFIRST 1
                           IF NOT EXISTS(SELECT KEYNO FROM MZSCHEDULE (NOLOCK) WHERE KeyNo = @ScheduleId AND (STATUS IS NULL OR STATUS=2) AND TIMEINTERVAL IS NOT NULL AND WEEKDAY=DATEPART(WEEKDAY,@RESERVATIONDATE_IN))
                           BEGIN
                               SELECT -1 AS RESULT,'预约目标不正确，请重新预约！' AS RESULTTEXT
                               RETURN
                           END
                           SELECT @TotNum=TotNum,@TOTNUM2=TOTNUM2,@DEPTCODE_IN=KSCode,@DOCTCODE_IN=YSCode,@TID_IN=TimeInterval,@GROUPPRICE_CALC=CheckMoney1+CheckMoney2
                           ,@DEPTNAME_HIS=KSName,@SPEC_HIS=SPECIALTY,@CLINICCODE_IN=CheckGroupCode,@GROUPNAME_HIS=CheckGroupName
                           ,@CheckMoney1=CheckMoney1,@CheckMoney2=CheckMoney2 FROM MZSCHEDULE (NOLOCK) WHERE KeyNo = @ScheduleId
                           SELECT @OtherAlreadyCount=COUNT(*) FROM AP_RESERVATIONSHEET(NOLOCK) WHERE DEPTCODE=@DEPTCODE_IN AND DOCTCODE=@DOCTCODE_IN AND TID=@TID_IN AND  DELDATE IS NULL AND RESERVATIONDATE=@RESERVATIONDATE_IN
                           SET @TOTNUM2=ISNULL(@TOTNUM2,0)
                           IF @TotNum>0
                           BEGIN
                               IF (@TOTNUM-@OTHERALREADYCOUNT<=0) OR (@OTHERALREADYCOUNT>=@TOTNUM2)
                               BEGIN
                                   SELECT -1 AS RESULT,'该医师号源已预约完了，请稍后再试！' AS RESULTTEXT
                                   RETURN
                               END
                           END
                           SELECT @DOCTNAME_HIS=NAME,@TITLECODE_HIS=YHFLAG,@TITLENAME_HIS=CASE YHFLAG WHEN 2 THEN '普通医师（初职）' WHEN 3 THEN '主任医师（高职）' WHEN 5 THEN '主治医师（中职）' WHEN 6 THEN '副主任（高）'END FROM YSCODE (NOLOCK) WHERE CODE=@DOCTCODE_IN
                           IF (@DOCTCODE_IN) = '*'
                           BEGIN
                               SET @DOCTCODE_IN = ''
                               SET @DOCTNAME_HIS = ''
                               SET @TITLECODE_HIS = '任意医师'
                               SET @TITLENAME_HIS = '任意医师'
                           END
                           INSERT INTO AP_ORDERSHEET
                                        ([TYPE]
                                        ,[DATE]
                                        ,[TIME]
                                        ,[MONEY]
                                        ,ORDERFROMTYPE
                                        ,ORDERFROMNAME
                                        ,OPERDATE
                                        ,TRANSNO
                                        ,DEVICEID
                                        ,BANKTRANSNO
                                        ,AliPayMode             
                                        ,AliPatient_type        
                                        ,AliPatient_id          
                                        ,AliUserCardType
                                        ,AliReplaceUserCardType 
                                        ,AliReplaceUserCardId   
                                        ,AliReplaceUserName     
                                        ,AliIpAddr              
                                        ,AliMacAddr   
                                        ,AliPayState
                                        ,AliStartTime
                                        ,AliEndTime
                                        ,AliUserCardId
                                        ,AliScheduleId
                                        ,DelDate
                                        ,DelTransNo
                                        )
                                  VALUES
                                        (2
                                        ,CAST(@CurrentDate AS Date)
                                        ,CAST(@CurrentDate AS Time(0))
                                        ,@GROUPPRICE_CALC
                                        ,@CallNo
                                        ,@CALLNAME
                                        ,@CurrentDate
                                        ,@TRANSNO_IN
                                        ,''
                                        ,''
                                        ,@PayMode             
                                        ,@Patient_type        
                                        ,@Patient_id          
                                        ,@UserCardType
                                        ,@ReplaceUserCardType 
                                        ,@ReplaceUserCardId   
                                        ,@ReplaceUserName     
                                        ,@IpAddr              
                                        ,@MacAddr
                                        ,2
                                        ,@startTime
                                        ,@endTime
                                        ,@userCardId
                                        ,@scheduleIdStr
                                        ,DATEADD(MINUTE,15,@CurrentDate)
                                        ,'临时占用填充-缴费时清空'
                                    )
                           INSERT INTO AP_RESERVATIONSHEET
                                        (PATIENTID
                                        ,OrderId
                                        ,DEPTCODE
                                        ,DEPTNAME
                                        ,DOCTCODE
                                        ,DOCTNAME
                                        ,TITLECODE
                                        ,TITLENAME
                                        ,SPEC
                                        ,TID
                                        ,CLINICCODE
                                        ,CLINICNAME
                                        ,CLINICFEE
                                        ,REGFEE
                                        ,TRANSNO
                                        ,RESERVATIONDATE
                                        ,RESERVATIONFROMTYPE
                                        ,RESERVATIONFROMNAME
                                        ,OPERDATE
                                        ,DelDate
                                        ,DelTransNo
                                        )
                                  SELECT @PATIENTID
                                        ,Id
                                        ,@DEPTCODE_IN
                                        ,@DEPTNAME_HIS
                                        ,@DOCTCODE_IN
                                        ,@DOCTNAME_HIS
                                        ,@TITLECODE_HIS
                                        ,@TITLENAME_HIS
                                        ,@SPEC_HIS
                                        ,@TID_IN
                                        ,@CLINICCODE_IN
                                        ,@GROUPNAME_HIS
                                        ,@CheckMoney2
                                        ,@CheckMoney1
                                        ,@TRANSNO_IN
                                        ,@RESERVATIONDATE_IN
                                        ,@CALLNO
                                        ,@CALLNAME
                                        ,@CurrentDate
                                        ,DATEADD(MINUTE,15,@CurrentDate)
                                        ,'临时占用填充-缴费时清空'
                                   FROM AP_ORDERSHEET(NOLOCK)
                                  WHERE TRANSNO=@TRANSNO_IN AND
                                        ORDERFROMTYPE=@CALLNO
                           SELECT 0 AS Result,'成功' AS ResultText,@TRANSNO_IN AS TransNo,OrderId AS KeyNo FROM AP_ReserVationSheet WHERE Id=scope_identity()  ";
            connection.Open();
            var trans = connection.BeginTransaction();

            try
            {
                ReservationInfo reservationInfo = connection.Query<ReservationInfo>(sql, new
                {
                    CardNo_P = CardNo,
                    CardType_P = CardType,
                    CALLNO_P = CallNo,
                    PatientName_P = Name,
                    SEX_P = Sex,
                    BIRTHDAY_P = BirthDay,
                    ADDRESS_P = Adress,
                    TEL_P = Tel,
                    IDCardNo_P = IdCardNo,
                    CallName_P = CallName,
                    RESERVATIONDATE_IN_P = ReservationDate,
                    ScheduleId_P = ScheduleId,
                    TRANSNO_IN_P = TransNo,
                    payMode_P = payMode,
                    patient_type_P = patient_type,
                    patient_id_P = patient_id,
                    userCardType_P = userCardType,
                    replaceUserCardType_P = replaceUserCardType,
                    replaceUserCardId_P = replaceUserCardId,
                    replaceUserName_P = replaceUserName,
                    ipAddr_P = ipAddr,
                    macAddr_P = macAddr,
                    startTime_P = startTime,
                    endTime_P = endTime,
                    userCardId_P = userCardId,
                    ScheduleIdStr_P = ScheduleIdStr
                }, trans).First<ReservationInfo>();
                trans.Commit();
                if (reservationInfo.result != 0)
                {
                    errorMessage = reservationInfo.resultText;
                    KeyNo = 0;
                    return false;
                }
                KeyNo = reservationInfo.KeyNo;
                errorMessage = "成功";
                return true;
            }
            catch (Exception e)
            {
                trans.Rollback();
                errorMessage = e.Message;
                KeyNo = 0;
                return false;
            }
            finally
            {
                trans.Dispose();
                connection.Close();
            }
        }
        //退号
        public bool OrderCancelInfoADO(int OrderId, string DelTransNo, out string errorMessage)
        {
            string sql = @"DECLARE @DELTRANSNO VARCHAR(50)
                         DECLARE @OrderId INT
                         
                         SET @OrderId = (@OrderId_P)
                         SET @DELTRANSNO=(@DELTRANSNO_P)
                         
                         
                         IF NOT EXISTS(SELECT TransNo FROM AP_ReservationSheet (NOLOCK) WHERE OrderId=@OrderId)
                         BEGIN
                           Select -1 as Result,'当天的预约只能到线下窗口进行退费或无此预约信息！' as ResultText
                           RETURN
                         END
                         IF EXISTS(SELECT TransNo FROM AP_ReservationSheet (NOLOCK) WHERE OrderId=@OrderId AND DelDate IS NOT NULL AND DelTransNo<>'临时占用填充-缴费时清空')
                         BEGIN
                           Select 0 as Result,'此预约流水号对应的记录已被取消预约！' as ResultText
                           RETURN
                         END
                         IF EXISTS(SELECT TransNo FROM AP_ReservationSheet (NOLOCK) WHERE OrderId=@OrderId AND CommitDate IS NOT NULL)
                         begin
                           Select -1 as Result,'此预约流水号对应的记录已被取号，不能取消预约！' as ResultText
                           RETURN
                         end
                         IF EXISTS(SELECT DelTransNo FROM AP_ReservationSheet (NOLOCK) WHERE TransNo=@DELTRANSNO or DelTransNo=@DELTRANSNO)
                         begin
                           Select -1 as Result,'取消预约流水号重复！' as ResultText
                           RETURN
                         end
                         IF (CAST(GETDATE() AS DATE)>=CAST((SELECT RESERVATIONDATE FROM AP_ReservationSheet (NOLOCK) WHERE OrderId=@OrderId) AS DATE))
                         BEGIN
                           Select -1 as Result,'只能在预约日期之前取消预约！' as ResultText
                           RETURN
                         END
                         
                         DECLARE @CurrentDate datetime
                         SET @CurrentDate=GETDATE()
                         
                         UPDATE AP_ReservationSheet
                            SET DelDate=@CurrentDate
                               ,DelTransNo=@DELTRANSNO
                          WHERE OrderId=@OrderId
                          
                         UPDATE AP_OrderSheet
                            SET DelDate=@CurrentDate
                               ,DelTransNo=@DELTRANSNO
                          WHERE Id=@OrderId
                         
                         SELECT 0 AS Result,'成功' AS ResultText,1 AS KeyNo";
            connection.Open();
            var trans = connection.BeginTransaction();

            try
            {
                ReservationInfo reservationInfo = connection.Query<ReservationInfo>(sql, new
                {
                    OrderId_P = OrderId,
                    DELTRANSNO_P = DelTransNo
                }, trans).First<ReservationInfo>();
                trans.Commit();
                if (reservationInfo.result != 0)
                {
                    errorMessage = reservationInfo.resultText;
                    return false;
                }
                errorMessage = "成功";
                return true;
            }
            catch (Exception e)
            {
                trans.Rollback();
                errorMessage = e.Message;
                return false;
            }
            finally
            {
                trans.Dispose();
                connection.Close();
            }
        }

        public List<OrderInfo> GetOrderListADO(string queryPersonType, string userCardType, string userCardId, string queryDateType, DateTime startTime, DateTime endTime)
        {
            var sql = @"DECLARE @queryPersonType VARCHAR(50)
                        DECLARE @userCardId VARCHAR(50)
                        DECLARE	@userCardType VARCHAR(50)
                        DECLARE @queryDateType VARCHAR(50)
                        DECLARE @startTime DATE
                        DECLARE @endTime DATE
                        
                        SET @queryPersonType=@queryPersonType_p
                        SET @userCardId=@userCardId_P
                        SET @userCardType=@userCardType_P
                        SET @queryDateType=@queryDateType_P
                        SET @startTime=@startTime_P
                        SET @endTime=@endTime_P
                        
                        SELECT A.Id AS orderId 
                              ,'43120477700' AS hosOrgCode
                              ,CASE WHEN F.ProcessDate IS NULL AND CAST(GETDATE() AS DATE)> B.ReservationDate THEN '4' 
                                    WHEN F.ProcessDate IS NOT NULL THEN '3' 
                                    WHEN B.DelDate IS NOT NULL AND B.DelTransNo<>'临时占用填充-缴费时清空' THEN '2'
                                    ELSE '1' END AS orderStatus
                              ,C.LBCODE AS oneDeptCode
                              ,B.DeptCode AS deptCode      
                              ,CASE WHEN ISNULL(RTRIM(B.DoctCode),'')='' THEN 2 ELSE 1 END AS orderType
                              ,CASE WHEN ISNULL(RTRIM(B.DoctCode),'')='' THEN '*' ELSE B.DoctCode END AS resourceCode      
                              ,A.AliScheduleId AS scheduleId
                              ,A.AliScheduleId AS numSourceId
                              ,A.AliScheduleId AS numSourceDetailId
                              ,'云南省第三人民医院' AS hosName
                              ,RTRIM(B.DeptName) AS deptName
                              ,CASE WHEN ISNULL(RTRIM(B.DoctCode),'')='' THEN RTRIM(B.DeptName) ELSE RTRIM(B.DoctName) END AS resourceName
                              ,CONVERT(varchar(20),B.ReservationDate,23) AS orderTime
                              ,CASE SUBSTRING(A.AliScheduleId,CHARINDEX('_',AliScheduleId) + 1,1) WHEN '1' THEN '1' WHEN '3' THEN '2' WHEN    '4'   THEN     '3'     END AS    timeRange
                              ,A.AliStartTime AS startTime
                              ,A.AliEndTime AS endTime
                              ,A.AliPayMode AS payMode
                              ,B.ClinicFee + B.RegFee AS visitCost
                              ,A.AliPayState AS payState 
                              ,RTRIM(E.PatientName) AS userName
                              ,E.Tel AS userPhone
                              ,convert(varchar(19),A.OperDate,121) AS applyDate
                              ,convert(varchar(19),B.DelDate,121) AS cancelDate  
                              ,CASE B.ClinicName
                                 WHEN '副主任医师挂号' THEN '102'
                                 WHEN '副主任医师挂号（减免）' THEN '102'
                                 WHEN '急诊挂号' THEN '999'
                                 WHEN '普通挂号' THEN '100'
                                 WHEN '主任医师挂号' THEN '102'
                                 WHEN '主任医师挂号（减免）' THEN '102'
                               END AS orderNumType
                          FROM dbo.AP_OrderSheet A(NOLOCK) 
                          JOIN dbo.AP_ReservationSheet B(NOLOCK) ON A.Id = B.OrderId
                          JOIN dbo.KSCODE C (NOLOCK) ON B.DeptCode = C.CODE
                          JOIN dbo.MZSchedule D (NOLOCK) ON SUBSTRING(A.AliScheduleId,1,CASE WHEN CHARINDEX('_',A.AliScheduleId) = 0 THEN   100   ELSE        CHARINDEX      ('_',A.AliScheduleId) END - 1)=CONVERT(VARCHAR(20),D.KeyNo)
                          JOIN dbo.PatientBase E (NOLOCK) ON B.PatientId = E.PatientId
                          LEFT JOIN dbo.MZRegisterSheet F (NOLOCK) ON B.MZNum = F.MZNum
                         WHERE 
                        ((@queryPersonType = '1' AND AliUserCardType=@userCardType AND AliUserCardId=@userCardId) OR
                         (@queryPersonType = '2' AND AliReplaceUserCardType=@userCardType AND AliReplaceUserCardId=@userCardId)) AND
                        ((@queryDateType = '1' AND B.ReservationDate>=@startTime AND B.ReservationDate<=@endTime) OR
                         (@queryDateType = '2' AND A.Date>=@startTime AND A.Date<=@endTime))";

            return connection.Query<OrderInfo>(sql, new
            {
                queryPersonType_p = queryPersonType,
                userCardId_P = userCardId,
                userCardType_P = userCardType,
                queryDateType_P = queryDateType,
                startTime_P = startTime,
                endTime_p = endTime
            }).ToList();
        }
        public OrderDetailsInfo GetOrderGetOrderDetailInfoADO(int OrderId)
        {
            var sql = @"DECLARE @orderId INT
                        SET @orderId=@orderId_P
                        SELECT @orderId AS orderId
                              ,'43120477700' AS hosOrgCode
                              ,CASE WHEN F.ProcessDate IS NULL AND CAST(GETDATE() AS DATE)> B.ReservationDate THEN '4' 
                                    WHEN F.ProcessDate IS NOT NULL THEN '3' 
                                    WHEN B.DelDate IS NOT NULL AND B.DelTransNo<>'临时占用填充-缴费时清空' THEN '2'
                                    ELSE '1' END AS orderStatus
                              ,RTRIM(C.LBCODE) AS oneDeptCode
                              ,RTRIM(B.DeptCode) AS deptCode      
                              ,CASE WHEN ISNULL(RTRIM(B.DoctCode),'')='' THEN 2 ELSE 1 END AS orderType
                              ,CASE WHEN ISNULL(RTRIM(B.DoctCode),'')='' THEN '*' ELSE B.DoctCode END AS resourceCode
                              ,A.OrderFromType AS channelCode
                              ,RTRIM(A.OrderFromName) AS channelName      
                              ,A.AliScheduleId AS scheduleId
                              ,A.AliScheduleId AS numSourceId
                              ,A.AliScheduleId AS numSourceDetailId
                              ,'云南省第三人民医院' AS hosName
                              ,RTRIM(B.DeptName) AS deptName
                              ,CASE WHEN ISNULL(RTRIM(B.DoctCode),'')='' THEN RTRIM(B.DeptName) ELSE RTRIM(B.DoctName) END AS resourceName
                              ,A.Money AS visitCost
                              ,convert(varchar(19),A.OperDate,121) AS applyDate
                              ,convert(varchar(19),B.DelDate,121) AS cancelDate
                              ,convert(varchar(19),B.ReservationDate,23) AS orderTime
                              ,CASE SUBSTRING(A.AliScheduleId,CHARINDEX('_',AliScheduleId) + 1,1) WHEN '1' THEN '1' WHEN '3' THEN '2' WHEN '4'    THEN     '3'     END AS    timeRange
                              ,A.AliStartTime AS startTime
                              ,A.AliEndTime AS endTime
                              ,A.AliPayMode AS payMode      
                              ,A.AliPayState AS payState 
                              ,RTRIM(E.PatientName) AS userName
                              ,A.AliPatient_type AS patient_type
                              ,A.AliUserCardType AS userCardType
                              ,A.AliUserCardId AS userCardId
                              ,RTRIM(E.PatientName) AS userName
                              ,E.Tel AS userPhone
                              ,CASE WHEN E.Sex='男' THEN 1 WHEN E.Sex = '女' THEN 2 ELSE '0' END AS userSex
                              ,A.AliReplaceUserCardType AS replaceUserCardType
                              ,A.AliReplaceUserCardId AS replaceUserCardId
                              ,A.AliReplaceUserName AS replaceUserName
                              ,CASE B.ClinicName
                                 WHEN '副主任医师挂号' THEN '102'
                                 WHEN '副主任医师挂号（减免）' THEN '102'
                                 WHEN '急诊挂号' THEN '999'
                                 WHEN '普通挂号' THEN '100'
                                 WHEN '主任医师挂号' THEN '102'
                                 WHEN '主任医师挂号（减免）' THEN '102'
                               END AS orderNumType
                        FROM dbo.AP_OrderSheet A(NOLOCK) 
                          JOIN dbo.AP_ReservationSheet B(NOLOCK) ON A.Id = B.OrderId
                          JOIN dbo.KSCODE C (NOLOCK) ON B.DeptCode = C.CODE
                          JOIN dbo.MZSchedule D (NOLOCK) ON SUBSTRING(A.AliScheduleId,1,CASE WHEN CHARINDEX('_',A.AliScheduleId) = 0 THEN 100   ELSE        CHARINDEX      ('_',A.AliScheduleId) END - 1)=CONVERT(VARCHAR(20),D.KeyNo)
                          JOIN dbo.PatientBase E (NOLOCK) ON B.PatientId = E.PatientId
                        LEFT JOIN dbo.MZRegisterSheet F (NOLOCK) ON B.MZNum = F.MZNum
                        WHERE A.Id=@orderId";

            return connection.Query<OrderDetailsInfo>(sql, new
            {
                OrderId_P = OrderId
            }).First();
        }

        //获取可预约医师信息和可预约门诊信息
        public List<ResourceDoct> GetResourceDoctInfoADO(string DeptCode, int Flag)
        {
            var sql = @"DECLARE @DEPTCODE VARCHAR(20)
                        DECLARE @CurrentDate DATE
                        DECLARE @Flag INT
                        
                        SET DATEFIRST 1;
                        SET @DEPTCODE=@DEPTCODE_P
                        SET @CurrentDate=CAST(GETDATE() AS DATE)
                        SET @Flag = @Flag_P
                        
                            SELECT '43120477700' AS hosOrgCode
                                  ,D.LBCODE AS oneDeptCode
                                  ,A.KSCode AS deptCode
                                  ,A.YSCode AS resourceCode
                                  ,'云南省第三人民医院' AS hosName
                                  ,D.LBNAME AS oneDeptName
                                  ,A.KSName AS deptName
                                  ,CASE WHEN A.YSCode='*' THEN A.KSName ELSE A.YSName END AS resourceName
                                  ,'1' AS personType
                                  ,'' AS personId
                                  ,'0' AS doctSex
                                  ,'0' AS isResource
                                  ,convert(varchar(19),CASE WHEN DATEPART(WEEKDAY,@CurrentDate)<A.WEEKDAY THEN
                                                                  DATEADD(DAY,A.WEEKDAY-DATEPART(WEEKDAY,@CurrentDate), @CurrentDate)
                                                             ELSE
                                                                  DATEADD(DAY,7+A.WEEKDAY-DATEPART(WEEKDAY,@CurrentDate),@CurrentDate) END,23)   AS         orderTime
                                  ,CASE WHEN A.TOTNUM>0 THEN CASE WHEN ISNULL(A.TOTNUM2,0)>ISNULL(C.ALREADYCOUNT,0) THEN ISNULL(A.TOTNUM2,0)-     ISNULL           (C.ALREADYCOUNT,0)
                                                             ELSE 0
                                                             END
                                   ELSE 0
                                   END AS remainNum
                              FROM MZSCHEDULE A(NOLOCK)
                              LEFT JOIN (select DoctCode,Reservationdate,COUNT(*) as alreadycount from AP_ReservationSheet (nolock) where DelDate is null and ReservationDate>cast(getdate() as date) AND ReservationDate<cast(getdate()+8 as date)  group by doctcode,Reservationdate) C
                              ON A.YSCODE=C.DOCTCODE AND A.WEEKDAY=DATEPART(WEEKDAY,C.Reservationdate)
                              LEFT JOIN dbo.KSCODE D (NOLOCK) ON A.KSCode=D.CODE
                              WHERE A.KSCODE=@DEPTCODE AND
                                   ((@Flag = 0 AND A.YSCode <> '*') or (@Flag = 1 AND A.YSCode = '*')) AND
                                   (A.StopEndDate IS NULL OR
                                     CASE WHEN A.Weekday <= DATEPART(WEEKDAY,GETDATE()) THEN CAST(GETDATE() + A.Weekday + 7 - DATEPART      (WEEKDAY,GETDATE  ())   AS    DATE)   ELSE CAST(GETDATE() + A.Weekday - DATEPART(WEEKDAY,GETDATE()) AS     DATE) END<A.StopBegDate OR
                                     CASE WHEN A.Weekday <= DATEPART(WEEKDAY,GETDATE()) THEN CAST(GETDATE() + A.Weekday + 7 - DATEPART      (WEEKDAY,GETDATE  ())   AS    DATE)   ELSE CAST(GETDATE() + A.Weekday - DATEPART(WEEKDAY,GETDATE()) AS     DATE) END > A.StopEndDate)    AND
                                   (A.STATUS IS NULL OR A.STATUS =2) AND
                                   (CASE WHEN A.TOTNUM>0 THEN CASE WHEN ISNULL(A.TOTNUM2,0)>ISNULL(C.ALREADYCOUNT,0) THEN 1
                                                                   ELSE 0
                                                                   END
                                                         ELSE 1
                                                         END=1)
                            ORDER BY RESERVATIONDATE
                        ";

            return connection.Query<ResourceDoct>(sql, new
            {
                DEPTCODE_P = DeptCode,
                @Flag_P = Flag

            }).ToList();
        }

        //退费/缴费
        public bool UpPayStatusADO(int OrderId, string BankTransNo, string PayMode, decimal PayAmount, string PayType0, out string errorMessage)
        {
            string sql = @"DECLARE @BankTransNo VARCHAR(50)
                           DECLARE @OrderId INT
                           DECLARE @PayMode VARCHAR(50)
                           DECLARE @PayAmount MONEY
                           DECLARE @PayType0 VARCHAR(20)
                         
                           SET @OrderId = (@OrderId_P)
                           SET @BankTransNo=(@BankTransNo_P)
                           SET @PayMode=@PayMode_p
                           SET @PayAmount=@PayAmount_p
                           SET @PayType0=@PayType0_p
                           
                           IF @PayType0='1' AND NOT EXISTS(SELECT A.TransNo FROM AP_ReservationSheet A(NOLOCK) JOIN AP_OrderSheet B(NOLOCK)   ON A.OrderId=B.Id WHERE A.OrderId=@OrderId AND B.DelDate>GetDate() AND B.DelTransNo='临时占用填充-缴费时清空')
                           BEGIN
                             Select -1 as Result,'此预约已过期作废或不存在！' as ResultText
                             RETURN
                           END
                           IF @PayType0='2' AND NOT EXISTS(SELECT A.TransNo FROM AP_ReservationSheet A(NOLOCK) JOIN AP_OrderSheet B(NOLOCK)   ON A.OrderId=B.Id WHERE A.OrderId=@OrderId AND A.DelDate IS NOT NULL)
                           BEGIN
                             Select -1 as Result,'此预约未缴费不能退费！' as ResultText 
                             RETURN
                           END
                           IF @PayType0='2' AND EXISTS(SELECT TransNo FROM AP_ReservationSheet (NOLOCK) WHERE OrderId=@OrderId AND DelDate IS NOT NULL AND DelTransNo = '临时占用填充-缴费时清空')
                           BEGIN
                             Select -1 as Result,'此预约流水号对应的记录已被取消预约！' as ResultText
                             RETURN
                           END
                           IF EXISTS(SELECT TransNo FROM AP_ReservationSheet (NOLOCK) WHERE OrderId=@OrderId AND CommitDate IS NOT NULL)
                           begin
                             Select -1 as Result,'此预约流水号对应的记录已被取号，不能操作！' as ResultText
                             RETURN
                           end
                           
                           IF (CAST(GETDATE() AS DATE)>=CAST((SELECT RESERVATIONDATE FROM AP_ReservationSheet (NOLOCK) WHERE   OrderId=@OrderId) AS DATE))
                           BEGIN
                             Select -1 as Result,'只能在预约日期之前操作！' as ResultText
                             RETURN
                           END
                           
                           DECLARE @CurrentDate datetime
                           SET @CurrentDate=GETDATE()
                           
                           IF @payType0 = '1' 
                           BEGIN
                              UPDATE AP_OrderSheet
                              SET DelDate=NULL
                                 ,DelTransNo=NULL
                                 ,BankTransNo=@BankTransNo
                                 ,AliPayMode=@payMode
                                 ,AliPayAmount=@PayAmount
                                 ,AliPayState='1'
                              WHERE Id=@OrderId
                           
                              UPDATE AP_ReservationSheet
                              SET DelDate=NULL
                                 ,DelTransNo=NULL
                            WHERE OrderId=@OrderId
                           END ELSE IF @payType0 = '2' 
                           BEGIN 
                               UPDATE AP_ReservationSheet
                                  SET DelDate=@CurrentDate
                                     ,DelTransNo=@BankTransNo
                                     
                                WHERE OrderId=@OrderId
                            
                               UPDATE AP_OrderSheet
                                  SET DelDate=@CurrentDate
                                     ,DelTransNo=@BankTransNo
                                     ,AliPayMode=@payMode
                                     ,AliPayAmount=@PayAmount
                                     ,AliPayState='3'
                                WHERE Id=@OrderId
                           END;
                           
                           SELECT 0 AS Result,'成功' AS ResultText,1 AS KeyNo";
            connection.Open();
            var trans = connection.BeginTransaction();

            try
            {
                ReservationInfo reservationInfo = connection.Query<ReservationInfo>(sql, new
                {
                    OrderId_P = OrderId,
                    BankTransNo_P = BankTransNo,
                    PayMode_P = PayMode,
                    PayAmount_P = PayAmount,
                    PayType0_P = PayType0
                }, trans).First<ReservationInfo>();
                trans.Commit();
                if (reservationInfo.result != 0)
                {
                    errorMessage = reservationInfo.resultText;
                    return false;
                }
                errorMessage = "成功";
                return true;
            }
            catch (Exception e)
            {
                trans.Rollback();
                errorMessage = e.Message;
                return false;
            }
            finally
            {
                trans.Dispose();
                connection.Close();
            }
        }
    }
}